VLOOKUP/ INDIRECT Date range isssue

[reddevilanalyst]

Hi All,

I have been working on a small invoice report. I have multiple sheets of invoices and to ease them up - I wanted to autofill whatever I type in the sheets to the INDEX page. I used the formula =VLOOKUP($B$2,INDIRECT("'"&$A3&"'!$A$15:$C$15"),3,FALSE) function to match cells from other sheets to the INDEX page. So basically I have created different headings for Company, created by, date and amount. The company and created list works fine with the formula but when I try the date range and amount - it shows as a number. I tried using another VLOOKUP(INDIRECT ddmmyyyy method too - but it is not working.

The dates are filled in the sheets between the range G3:H3 and similarly for the amount it is J43:L43. Please suggest any help/ any different formulas which can help me out. I have nearly 50 sheets and it will be easy to auto-fill the INDEX page when I type in the individual sheets

 

[Bosquedeguate]

do you have the target worksheet's cells formatted as a date? (like mm/dd/yyyy)? you might just be seeing the serial number that represents a date.

 

[Bosquedeguate]

Thanks this worked. Hurray !! So, I don't need VLOOKUP for that, that is where I did the mistake. Thank you so much for your patience. <3

No problem. I was just difficult for me to know where data was coming from and where is needed to be.
So, if you update L59, then Cell E3 on your other sheet will automatically update.
Good luck with your project, glad I could help.

 

[reddevilanalyst]

do you have the target worksheet's cells formatted as a date? (like mm/dd/yyyy)? you might just be seeing the serial number that represents a date.

Thanks for that - I figured it out now. I have trouble doing the same for amount - I can apply the formula when I do it adjacent column-wise (yellow highlight)- =VLOOKUP($E$2,INDIRECT("'"&$A3&"'!$J$60:$K$60"),2,FALSE) and it gives out the correct answer but for adjacent row-wise (highlighted in green) - INDIRECT function does not come up (attached). Any idea, what changes need to be made to the formula?

 

[Bosquedeguate]

What kind of data do you have in E2? String or numeric value?

 

[reddevilanalyst]

E2 - is the index page - header for Total Amount as you can see.

 

[Bosquedeguate]

if the data you need is coming from the same table, then you want the Total Amount column
I think you want to use the same formula, just change the 2 (third) argument, to a 5. Right?

How do the amounts differ (Yellow Total and Green Total)? Where should these values be coming from?

 

[reddevilanalyst]

I tried but doesn't help. I just want the total amount to be auto-filled into the index page when I type in each sheets.

Here you can see when I use the same formula for Company name, and date it works as it is adjacent columns. for the Total, the amount 200 is beneath it - that never comes. If I try =VLOOKUP($E$2,INDIRECT("'"&$A3&"'!$J$60:$K$60"),1,FALSE) - it shows Total in E3 but when I try 2,False - it gives me error.

 

[Bosquedeguate]

I am missing something ...
the value of $E$2 in your VLOOKUP function is a Tab No. value to do a lookup in the INDEX table? Right?
Are you trying to pull a value from your index table or put a value into the table in Column E3 (for example).

If you are getting data from the table (again for the example of Tab No.:4186) the VLOOKUP function for getting the total would be:
=VLOOKUP(VLOOKUP($E$2,INDIRECT("'"&$A3&"'!$J$60:$K$60"),1,FALSE) like you explained and said it works

What I am not understanding is what value do you want for the Total (Cell L59) in your right-hand screenshot.
Do you want to pull this value from somewhere? (If so, from where - table, row, and column). Or
Do you want to put this value into another cell? (If so, what is going to receive it - sheet, row, column).

I am working blind by not having a full understanding of your data.

 

[reddevilanalyst]

Hi, Sorry for the confusion. Just like I did for company name, created by, date - I want to do the same. Basically, pull the data from L59 i.e. £200 and fill that in E3 under Total. That is all I need. L59 cell will be constant over every sheet.

For eg: The company name for 4186 tab - I used this formula: =VLOOKUP($B$2,INDIRECT("'"&$A3&"'!$A$15:$C$15"),3,FALSE) and it pulled data from 4186 - from cells A15:C15 where A15 - company name and C15: Swift. Similarly I want it to try for Total.