# Posts by stilton

• ## AND statement using multiple VLOOKUP criteria

Thanks for the help! I'm not sure why I wasn't using COUNTIFS rather than VLOOKUP since I was just verifying. I was having an issue however with the Variety column if it was blank but created an extra if statement to only check columns YEAR and DESIGN if VARIETY was blank. I'm sure it's not optimized code but it seems to be working.

=IFERROR(IF(VARIETY=0,IF(AND(COUNTIFS(A:A,Z3,B:B,AA3)),1,0),IF(AND(COUNTIFS(A:A,Z3,B:B,AA3,C:C,AB3)),1,0)),0)

• ## AND statement using multiple VLOOKUP criteria

I have a spreadsheet itemizing a coin collection and would like to have a conditional formula confirm if I have a coin listed in the spreadsheet based on a checklist. My formula is working but only partially with coins that are similar. The spreadsheet is setup as such:

 YEAR COIN DESIGN VARIETY QUANTITY 1858 Flying Eagle Small Letters 1 1858 Flying Eagle Large Letters 1

I have many rows of data like the ones above and in another column I have the following formula essentially checking to see:

1. If a quantity exists for a given year

2. That year also has a design called "Flying Eagle"

3. That year and design also has a variety of "Small Letters"

I replaced the cell location with the words QUANTITY, DESIGN, and quoted words to simplify the expression.

=IF(AND(VLOOKUP(QUANTITY,\$A:\$D,4,FALSE)>0,VLOOKUP(DESIGN,\$A:\$B,2,FALSE)="Flying Eagle",VLOOKUP("Small Letters",\$A:\$C,3,FALSE)="Small Letters"),1,0)

This works fantastic confirming the first coin with small letters is present, but subsequent coins are not getting confirmed present unless the match the first coin, and no I don't actually have "Small Letters" hardcoded but rather references another cell so the next VLOOKUP statement would search for "Large Letters" but tells me it's not there. I thought the TRUE/FALSE might be the cause in the VLOOKUP statement but didn't work.

I did search the forum and wasn't able to find anything so I apologize in advance otherwise as I know this most likely has a simple solution.

Thanks

• ## Transposing Data Macro

I created a calendar with a lot of data fields that need to be entered daily. This data gets copied to a master schedule, but occasionally needs to be edited. I created a macro that simply copies and pastes the master calendar data back into the editable calendar so users don't have to start over, however I'm wondering if a better way to do this is possible as it takes several minutes to use the copy/paste macro I recorded to complete. Here's a snippet of the macro:

I've been trying to find an easier way to link thousands of spreadsheets filled with data using a single workbook that pulls specific information into one source. I have column A listing thousands of store numbers statically with one store per row. Columns B-BZ then use hard links to thousands of individual files for each store number that's saved on my flash drive to pull the data needed. This works perfectly ok but if I change the store number in column A, the other columns won't update because of the link.

Example:

A1 = 1 B1='J:\Data\2021\Dec\[1.xlsx]Prelim'!\$D\$7 C1='J:\Data\2021\Dec\[1.xlsx]Audit Entry'!\$M\$10

A2 = 2 B2='J:\Data\2021\Dec\[2.xlsx]Prelim'!\$D\$7 C2='J:\Data\2021\Dec\[2.xlsx]Audit Entry'!\$M\$10

A3 = 3 B3='J:\Data\2021\Dec\[3.xlsx]Prelim'!\$D\$7 C3='J:\Data\2021\Dec\[3.xlsx]Audit Entry'!\$M\$10

Is it possible to dynamically link the external worksheet such as [1.xlsx] somehow? I tried entering as [A1.xlsx] but wouldn't work and haven't been able to find a solution. Any assistance would be greatly appreciated.